﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_SearchTrainingRecord

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal VolunteerId As String, ByVal RegStatus As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT B.GroupName,B2.[Text] as CourseTypeText,D.CourseName,B.RegStartDate,B.RegEndDate,B.StartDate,B.EndDate,A2.[Text] as RegStatusText ")
        sql.AppendFormat("FROM TrainingRegister A  ")
        sql.AppendFormat("inner join CodeMapping A2 on A.RegStatus=A2.Value and A2.TableName='TrainingRegister' and A2.ColumnName='RegStatus' ")
        sql.AppendFormat("inner join TrainingCourseGroup B on A.GroupId = B.Id  ")
        sql.AppendFormat("inner join CodeMapping B2 on B.GroupType=B2.Value and B2.TableName='TrainingCourseGroup' and B2.ColumnName='GroupType' ")
        sql.AppendFormat("inner join TrainingCourseGroupMapping C on B.Id = C.GroupId ")
        sql.AppendFormat("inner join TrainingCourse D on C.CourseId = D.Id ")
        sql.AppendFormat("WHERE A.VolunteerId='{0}' ", VolunteerId)
        sql.AppendFormat("AND (A.RegStatus='{0}' or '{0}'='') ", RegStatus)

        Return dat.GetData(sql)

    End Function

End Class
